package com.erp.app.report.genericInquiry.inquiryOutput.dao;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.ArrayList;
import java.util.Collection;

import com.erp.app.report.genericInquiry.inquiryDisplay.beans.vo.InquiryDisplayVO;
import com.erp.app.report.genericInquiry.inquiryMaster.dao.GenericInquirySearchCriteria;
import com.erp.app.report.genericInquiry.inquiryOutput.beans.vo.InquiryOutputVO;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.pagination.PageSearchCriteria;
import com.erp.framework.util.CommonUtil;
import com.erp.framework.util.logger.Logger;
import com.erp.app.common.ProjectConstants;

public class InquiryOutputDAO extends SimplePageEditStyleDao {
	
	public Collection findByCriteria(Object obj, Connection conn) throws FindRecordException {
        GenericInquirySearchCriteria criteria = (GenericInquirySearchCriteria) obj;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList rslt = new ArrayList();

        try {
            String query = sqlQuery(criteria);
            super.logSqlQuery(query);
            stmt = conn.createStatement();

            if (criteria.getSrchReport() == ProjectConstants.GENERIC_REPORT_TYPE_PAGE)
                stmt.setMaxRows(ProjectConstants.DB_ROW_NUM);
            else
                stmt.setMaxRows(ProjectConstants.EXCEL_ROW_NUM);

            rs = stmt.executeQuery(query);

            if (rs != null) {
                while (rs.next()) {
                    rslt.add(criteria.isSearchResult() ? getSearchResultSetValues(rs) : getResultSetValues(rs));
                }
            }

        } catch (Exception e) {
            Logger.logExceptions(this, e);
            throw new FindRecordException(e.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
        }
        return rslt;
    }
	
//  to be implemented when needed
    protected String sqlPurge(){return null;}

//  to be implemented when needed
    protected void setValuesForPurge(PreparedStatement pStmt, Object obj) throws SQLException{}

//  to be implemented when needed
    protected String sqlUpdate(){return null;}

//  to be implemented when needed
    protected void setValuesForModify(PreparedStatement pStmt, Object obj) throws SQLException{}
    
//  to be implemented when needed
    protected String sqlFind(){return null;}

//  to be implemented when needed
    protected void setValuesForFind(PreparedStatement pStmt, Object obj) throws SQLException{}

//  to be implemented when needed
    protected String sqlInsert(){return null;}

//  to be implemented when needed
    protected void setValuesForInsert(PreparedStatement pStmt, Object obj) throws SQLException{}

//  to be implemented when needed
    protected String sqlDelete(){return null;}

//  to be implemented when needed
    protected void setValuesForDelete(PreparedStatement pStmt, Object obj) throws SQLException{}

//  to be implemented when needed
    protected String sqlList(){return null;}
    
//  to be implemented when needed
    protected void setValuesForList(PreparedStatement pStmt, Object bizModel)
    throws SQLException {}
    
    protected String sqlQuery(SimpleBaseSrchCriteria criteria) {
        GenericInquirySearchCriteria crit = (GenericInquirySearchCriteria) criteria;
        Iterator dispIt = crit.getCriteriaList().iterator();
        InquiryDisplayVO displayVO = null;

        StringBuffer sb = new StringBuffer();
        sb.append("SELECT COMPANY_CODE, INQUIRY_TYPE, RPT_TIME, LINE_NO");

        while (dispIt.hasNext()) {
            displayVO = (InquiryDisplayVO) dispIt.next();
            sb.append(", " + displayVO.getDispCode());
        }

        sb.append(", CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, URL ");
        sb.append(" FROM T_INQUIRY_OUTPUT WHERE 1 = 1");
        sb.append(toWhereClause(crit));
        sb.append(" ORDER BY LINE_NO");
        
        super.logSqlQuery(sb.toString());

        return sb.toString();
    }

    private String toWhereClause(GenericInquirySearchCriteria criteria) {
        StringBuffer whereClause = new StringBuffer();

        if (!CommonUtil.isEmpty(criteria.getSrchCompanyCode()))
            whereClause.append(" AND COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase() + "'");

        if (!CommonUtil.isEmpty(criteria.getSrchInquiryType()))
            whereClause.append(" AND INQUIRY_TYPE = '" + criteria.getSrchInquiryType().trim().toUpperCase() + "'");

        if (!CommonUtil.isEmpty(criteria.getSrchRptTime()))
            whereClause.append(" AND RPT_TIME = '" + criteria.getSrchRptTime().trim().toUpperCase() + "'");

        if (criteria instanceof PageSearchCriteria && (InquiryOutputVO)((PageSearchCriteria)criteria).getRecordBreaker() != null) 
            whereClause.append(" AND line_no > ?");
      
        return whereClause.toString();
    }
    
//    protected String toWhereClause(Object obj)
//	{
//	    GenericInquirySearchCriteria criteria = (GenericInquirySearchCriteria) obj;
//
//	    StringBuffer whereClause = new StringBuffer();
//
//		whereClause.append(" WHERE 1=1");
//
//		if (!CommonUtil.isEmpty(criteria.getSrchCompanyCode())) {
//			whereClause.append(" AND COMPANY_CODE = ?");
//		}
//		if (!CommonUtil.isEmpty(criteria.getSrchInquiryType())) {
//			whereClause.append(" AND INQUIRY_TYPE = ?");
//		}
//		if (!CommonUtil.isEmpty(criteria.getSrchRptTime())) {
//			whereClause.append(" AND RPT_TIME = ?");
//		}
//		
//		if (obj instanceof PageSearchCriteria && (InquiryOutputVO)((PageSearchCriteria)obj).getRecordBreaker() != null) {
//				whereClause.append(" AND line_no > ?");
//		}
//
//		return whereClause.toString();
//	}

	protected void setValuesForCriteria(PreparedStatement pStmt, Object obj)
	throws SQLException { 
	    GenericInquirySearchCriteria criteria = (GenericInquirySearchCriteria) obj;
		int i = 1;
		if (!CommonUtil.isEmpty(criteria.getSrchCompanyCode())) {
			setNullableString(pStmt, i++, criteria.getSrchCompanyCode().trim().toUpperCase());
		}
		if (!CommonUtil.isEmpty(criteria.getSrchInquiryType())) {
			setNullableString(pStmt, i++, criteria.getSrchInquiryType().trim().toUpperCase());
		}
		if (!CommonUtil.isEmpty(criteria.getSrchRptTime())) {
			setNullableString(pStmt, i++, criteria.getSrchRptTime().trim().toUpperCase());
		}
		if (obj instanceof PageSearchCriteria) {
			InquiryOutputVO vo = (InquiryOutputVO)((PageSearchCriteria)obj).getRecordBreaker();
			
			if (vo != null)
				pStmt.setInt(i++, vo.getLineNo().intValue());
		}
		
	}
	
	protected String findByCriteriaOrderBy(SimpleBaseVo model, Object obj)
	{
		return " ORDER BY INQUIRY_TYPE, LINE_NO";
	}

    protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {
        InquiryOutputVO inquiryOutputVO = new InquiryOutputVO();
        inquiryOutputVO.setCompanyCode(rs.getString("COMPANY_CODE"));
        inquiryOutputVO.setInquiryType(rs.getString("INQUIRY_TYPE"));
        inquiryOutputVO.setRptTime(rs.getString("RPT_TIME"));
        inquiryOutputVO.setLineNo(rs.getBigDecimal("LINE_NO"));

        ResultSetMetaData md = rs.getMetaData();
        int colSize = md.getColumnCount();

        //for (int i = 5; i < colSize - 4; i++)
        for (int i = 5; i < colSize; i++)
            inquiryOutputVO.getFields().add(rs.getString(i));

        inquiryOutputVO.setCreateUser(rs.getString("CREATE_USER"));
        inquiryOutputVO.setCreateDate(rs.getTimestamp("CREATE_DATE"));
        inquiryOutputVO.setUpdateUser(rs.getString("UPDATE_USER"));
        inquiryOutputVO.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
        inquiryOutputVO.setURL(rs.getString("URL"));
        return inquiryOutputVO;
    }
    
//	protected SimpleBaseVo getResultSetValues(SimpleBaseVo bizModel, ResultSet rs)
//	throws SQLException, Exception
//	{		
//		SimpleBaseVo model = (SimpleBaseVo) bizModel.getClass().newInstance();
//		InquiryOutputVO vo = (InquiryOutputVO) model;
//		
//		ArrayList a = vo.getFields(); 
//
//		vo.setCompanyCode(rs.getString("COMPANY_CODE"));
//		vo.setInquiryType(rs.getString("INQUIRY_TYPE"));
//		vo.setRptTime(rs.getString("RPT_TIME"));
//		vo.setLineNo(rs.getBigDecimal("LINE_NO"));
//
//        ResultSetMetaData md = rs.getMetaData();
//        int colSize = md.getColumnCount();
//
//        for (int i = 5; i < colSize - 5; i++)
//            a.add(rs.getString(i));
//
//        vo.setCreateUser(rs.getString("CREATE_USER"));
//        vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
//        vo.setUpdateUser(rs.getString("UPDATE_USER"));
//        vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
//        vo.setURL(rs.getString("URL"));
//		
//		return model;
//	}
}
